package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.SysCode;
import com.telecom.cms.dto.SysCodeEx;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class SysCodeDAO {

	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(SysCode transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db + ".Sys_Code where code_type='"
				+ transientInstance.getCodeType() + "' and code_id='"
				+ transientInstance.getCodeId() + "' and code_type='"+transientInstance.getCodeType()+"'";
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Sys_Code set code_type='"

				+ transientInstance.getCodeType() + "',code_id='"
						+ transientInstance.getCodeId() + "',code_name='"
						+ transientInstance.getCodeName() + "',par_code_id='"
						+ transientInstance.getParCodeId() + "',code_desc='"
						+ transientInstance.getCodeDesc() + "',Creator_Name='"
						+ transientInstance.getCodeDesc()
						+ "',Create_Time=timestamp'"
						+ CurrentTime.getCurrentTime() + "' where code_id='"
						+ transientInstance.getCodeId() + "' and code_type='"+transientInstance.getCodeType()+"'";
			else
				sql = "insert into " + db + ".Sys_Code values('"
						+ transientInstance.getCodeType() + "','"
						+ transientInstance.getCodeId() + "','"
						+ transientInstance.getCodeName() + "','"
						+ transientInstance.getParCodeId() + "','"
						+ transientInstance.getCodeDesc() + "','"
						+ transientInstance.getCreatorName() + "',timestamp'"
						+ CurrentTime.getCurrentTime() + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(SysCode persistentInstance) {

		String sql = "delete from " + db + ".Sys_Code where Code_type='"
				+ persistentInstance.getCodeType() + "' and Code_Id='"
				+ persistentInstance.getCodeId() + "'  and code_type='"+persistentInstance.getCodeType()+"'";

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public SysCode findById(String codetype, String id) {

		String sql = "select * from " + db + ".Sys_Code where Code_type='"
				+ codetype + "' and Code_Id='" + id + "'";
		SysCode sd = new SysCode();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setCodeType(rs.getString(1));
				sd.setCodeId(id);
				sd.setCodeName(rs.getString(3));
				sd.setParCodeId(rs.getString(4));
				sd.setCodeDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll(String codetype, int i) {

		String sql = "";
		if (i == 1) {
			sql = "select * from " + db + ".Sys_Code where Code_Type = '"
					+ codetype + "' and Par_Code_Id in ('-1') order by Code_Id";
		} else {

			sql = "select * from " + db + ".Sys_Code where Code_Type = '"
					+ codetype + "' and Par_Code_Id not in ( '-1') order by Code_Id";
		}

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysCode sd = new SysCode();

				sd.setCodeType(rs.getString(1));
				sd.setCodeId(rs.getString(2));
				sd.setCodeName(rs.getString(3));
				sd.setParCodeId(rs.getString(4));
				sd.setCodeDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));

				list.add(sd);
			}
			
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}

	public List findAllByType(String codetype) {

		String sql = "select a.*,b.Code_Name Par_Code_Name from " + db
				+ ".Sys_Code a left join " + db + ".Sys_Code b "
				+ "on a.Par_Code_Id = b.Code_Id and b.Code_Type='" + codetype
				+ "' where a.Code_Type='" + codetype + "' order by a.code_id";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysCodeEx sd = new SysCodeEx();

				sd.setCodeType(rs.getString(1));
				sd.setCodeId(rs.getString(2));
				sd.setCodeName(rs.getString(3));
				sd.setParCodeId(rs.getString(4));
				sd.setCodeDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				sd.setParCodeName(rs.getString(8));

				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAllByTypeLvl(String codetype,String lvl) {

		String sql = null;
		if(lvl.equals("-1"))
		
			sql="select a.*,b.Code_Name Par_Code_Name from " + db
				+ ".Sys_Code a left join " + db + ".Sys_Code b "
				+ "on a.Par_Code_Id = b.Code_Id and b.Code_Type='" + codetype
				+ "' where a.Code_Type='" + codetype + "' and a.par_code_id='-1' order by a.code_id";
		else
			sql="select a.*,b.Code_Name Par_Code_Name from " + db
			+ ".Sys_Code a left join " + db + ".Sys_Code b "
			+ "on a.Par_Code_Id = b.Code_Id and b.Code_Type='" + codetype
			+ "' where a.Code_Type='" + codetype + "' and a.par_code_id<>'-1' order by a.code_id";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysCodeEx sd = new SysCodeEx();

				sd.setCodeType(rs.getString(1));
				sd.setCodeId(rs.getString(2));
				sd.setCodeName(rs.getString(3));
				sd.setParCodeId(rs.getString(4));
				sd.setCodeDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				sd.setParCodeName(rs.getString(8));

				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}

}